Tanana

Load raw WSE data

library(readr)
library(dplyr)
library(DT)
Tanana_WSE <- data.frame(read_csv("../input/TOWNS/Raw/TananaWSE.csv", col_names = TRUE)[,c(1:15)])
Tanana_WSE$time <- as.POSIXct(paste0(Tanana_WSE$Date, Tanana_WSE$Time_min), format = "%m/%d/%Y%H:%M:%S")+10800

attr(Tanana_WSE$time, "tzone") <- "America/Los_Angeles"
Tanana_WSE_df <- Tanana_WSE[,c(16, 4:15)]
colnames(Tanana_WSE_df)[c(2:13)] = c(1:12)

Load discharge data from USGS

library(dataRetrieval)
siteNumber <- "15515500"
parameterCd <- "00060"
convMetric <- 0.0283168466
Tanana_USGS_Q <- readNWISuv(site = siteNumber, parameterCd = parameterCd,startDate = as.Date(min(Tanana_WSE_df$time)), 
                                      endDate = as.Date(max(Tanana_WSE_df$time)+86400))
Tanana_USGS_Q_df <- data.frame(time = Tanana_USGS_Q$dateTime, Q = Tanana_USGS_Q$X_00060_00000*convMetric)

Combine both data sets (discharge and WSE)

library(dplyr)
library(tidyr)
library(zoo)

#turn WSE values of zero to NA, interpolate WSE to get to 15 minute resolution instead of 30 minute resolution.
Tanana_trimmed <- Tanana_WSE_df %>% gather(key = "ID", value = "WSE", -time) %>% 
  mutate(WSE = na_if(WSE, 0)) %>% spread(value = WSE, key = ID) %>% drop_na()

#filter discharge data to match dates
Tanana_long <- Tanana_USGS_Q_df %>% 
  filter(time >= min(Tanana_trimmed$time))%>% 
  filter(time <=max(Tanana_trimmed$time))%>%
  left_join(Tanana_trimmed) %>% 
  gather(key = "ID", value = "WSE", -time, - Q) %>% 
  mutate(WSE = na.approx(WSE))
  
Tanana_long$ID <- as.numeric(Tanana_long$ID)
datatable(Tanana_long %>% spread(value = WSE, key = ID), caption = "Tanana")

Plot WSE

library(ggplot2)

ggplot(data = Tanana_long, aes(x = time, y = WSE, color = ID, group = ID))+geom_point()+ggtitle("Tanana")

Write and save 15-minute, hourly, 6-hourly, and daily
library(dplyr)
library(tidyr)
Tanana_long %>% group_by(Date = format(time, "%Y-%m-%d"), ID)%>%
  summarize(WSE = mean(WSE, na.rm = TRUE))
## # A tibble: 396 x 3
## # Groups:   Date [33]
##    Date          ID   WSE
##    <chr>      <dbl> <dbl>
##  1 2015-05-19     1  136.
##  2 2015-05-19     2  135.
##  3 2015-05-19     3  134.
##  4 2015-05-19     4  133.
##  5 2015-05-19     5  132.
##  6 2015-05-19     6  131.
##  7 2015-05-19     7  130.
##  8 2015-05-19     8  128.
##  9 2015-05-19     9  127.
## 10 2015-05-19    10  124.
## # ... with 386 more rows
Tanana_WSE_Q_daily <- Tanana_long %>% group_by(Date = format(time, "%Y-%m-%d"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE), Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

Tanana_WSE_Q_hourly <- Tanana_long %>% group_by(Date = format(time, "%Y-%m-%d %H"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE), Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

every6hrseq <- seq.POSIXt(min(Tanana_long$time), max(Tanana_long$time), by='6 hour')

Tanana_WSE_Q_6_hrs <-  Tanana_long %>% group_by(Date = cut(time, breaks=every6hrseq), ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

Tanana_WSE_Q_15_min <-  Tanana_long %>% group_by(Date = time, ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

saveRDS(Tanana_WSE_Q_daily, "../input/TOWNS/WSE/Tanana_WSE_Q_daily.rds")
saveRDS(Tanana_WSE_Q_hourly, "../input/TOWNS/WSE/Tanana_WSE_Q_hourly.rds")
saveRDS(Tanana_WSE_Q_6_hrs, "../input/TOWNS/WSE/Tanana_WSE_Q_6_hrs.rds")
saveRDS(Tanana_WSE_Q_15_min, "../input/TOWNS/WSE/Tanana_WSE_Q_15_min.rds")

Olentangy

Load raw WSE data

library(readr)
Olentangy_WSE <- t(read_csv("../input/TOWNS/Raw/OlentangyH.csv", col_names = FALSE))[,1:20]
Olentangy_WSE_df <- data.frame(WSE = as.numeric(Olentangy_WSE), 
                              time = rep(c(1:nrow(Olentangy_WSE)), times = ncol(Olentangy_WSE)),
                              ID = rep(c(1:ncol(Olentangy_WSE)), each = nrow(Olentangy_WSE)))

Load discharge data (from both USGS, and Tuozzolo et al., 2019 (https://doi.org/10.1016/j.jhydrol.2019.06.038). Tuozzolo et al., 2019 is interpolated from the USGS record to the resolution of the data at 5 minutes. Both are used to match up the timing, and convert to 15 minute resolution.

library(dataRetrieval)
Olentangy_Q <- t(read_csv("../input/TOWNS/Raw/OlentangyQ.csv", col_names = FALSE))
siteNumber <- "03226800"
parameterCd <- "00060"
convMetric <- 0.0283168466
Olentangy_USGS_Q <- readNWISuv(site = siteNumber, parameterCd = parameterCd, startDate = "2014-12-04", endDate = "2014-12-18")
Olentangy_USGS_Q_df <- data.frame(time = Olentangy_USGS_Q$dateTime, Q = Olentangy_USGS_Q$X_00060_00000*convMetric)
Olentangy_USGS_Q_5min <- approx(as.POSIXct(Olentangy_USGS_Q_df$time), 
                                Olentangy_USGS_Q_df$Q, seq(as.POSIXct("2014-12-04"), as.POSIXct("2014-12-18"), by = 5*60))
offset <- which.max(Olentangy_USGS_Q_5min$y) - which.max(Olentangy_Q)

Olentangy_Q_5min <- data.frame(time = Olentangy_USGS_Q_5min$x[offset:(length(Olentangy_Q)+offset-1)], 
                               Q = Olentangy_USGS_Q_5min$y[offset:(length(Olentangy_Q)+offset-1)], 
                               ID = "gauge")

Combine both data sets

library(DT)
Olentangy_data_5min <- Olentangy_WSE_df
Olentangy_data_5min$time <- Olentangy_Q_5min$time
Olentangy_data_5min$Q <- Olentangy_Q_5min$Q
datatable(Olentangy_data_5min)

Plot WSE

library(ggplot2)
ggplot(data = Olentangy_data_5min, aes(x = time, y = WSE, color = ID, group = ID))+geom_point()

Write and save 15-minute, hourly, 6-hourly, and daily
library(dplyr)
library(tidyr)
Olentangy_WSE_Q_daily <- Olentangy_data_5min %>% group_by(Date = format(time, "%Y-%m-%d"), ID) %>%
  summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

Olentangy_WSE_Q_hourly <- Olentangy_data_5min %>% group_by(Date = format(time, "%Y-%m-%d %H"), ID) %>%
  summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

every6hrseq <- seq.POSIXt(min(Olentangy_data_5min$time), max(Olentangy_data_5min$time), by='6 hour')

Olentangy_WSE_Q_6_hrs <-  Olentangy_data_5min %>% group_by(Date = cut(time, breaks=every6hrseq), ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

every15minseq <- seq.POSIXt(min(Olentangy_data_5min$time), max(Olentangy_data_5min$time), by='15 min')

Olentangy_WSE_Q_15_min <-  Olentangy_data_5min %>% group_by(Date = cut(time, breaks=every15minseq), ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

saveRDS(Olentangy_WSE_Q_daily, "../input/TOWNS/WSE/Olentangy_WSE_Q_daily.rds")
saveRDS(Olentangy_WSE_Q_hourly, "../input/TOWNS/WSE/Olentangy_WSE_Q_hourly.rds")
saveRDS(Olentangy_WSE_Q_6_hrs, "../input/TOWNS/WSE/Olentangy_WSE_Q_6_hrs.rds")
saveRDS(Olentangy_WSE_Q_15_min, "../input/TOWNS/WSE/Olentangy_WSE_Q_15_min.rds")

Willamette

Load raw WSE data

library(stringr)
Willamette_WSE <- data.frame(read.table("../input/TOWNS/Raw/Willamette2015WSE.txt", fill = TRUE, header = TRUE))
Willamette_WSE$HHMMSS <- str_pad(Willamette_WSE$HHMMSS, 6, pad = "0")
Willamette_WSE$time <- as.POSIXct(paste0(Willamette_WSE$YYYYMMDD, Willamette_WSE$HHMMSS), format = "%Y%m%d%H%M")+10800
attr(Willamette_WSE$time, "tzone") <- "America/Los_Angeles"
Willamette_WSE_df <- Willamette_WSE[,c(24, 3:23)]
colnames(Willamette_WSE_df)[c(2:22)] = c(1:21)

Load discharge data from USGS

library(dataRetrieval)
siteNumber <- "14166000"
parameterCd <- "00060"
convMetric <- 0.0283168466
Willamette_USGS_Q <- readNWISuv(site = siteNumber, parameterCd = parameterCd,startDate = as.Date(min(Willamette_WSE_df$time)), 
                                      endDate = as.Date(max(Willamette_WSE_df$time)+86400))
Willamette_USGS_Q_df <- data.frame(time = Willamette_USGS_Q$dateTime, Q = Willamette_USGS_Q$X_00060_00000*convMetric)

Combine both data sets (discharge and WSE)

library(dplyr)
library(tidyr)

#filter discharge data to match dates

Willamette_Q_filt <- Willamette_USGS_Q_df %>% 
  filter(time >= min(Willamette_WSE_df$time))%>% 
  filter(time <=max(Willamette_WSE_df$time))%>%
  left_join(Willamette_WSE_df)

datatable(Willamette_Q_filt, caption = "Willamette")

Plot WSE

library(ggplot2)
Willamette_long <- Willamette_Q_filt %>% gather(key = "ID", value = "WSE", -time, -Q)
Willamette_long$ID <- as.numeric(Willamette_long$ID)

ggplot(data = Willamette_long, aes(x = time, y = WSE, color = ID, group = ID))+geom_point()+ggtitle("Willamette")

Write and save 15-minute, hourly, 6-hourly, and daily
library(dplyr)
library(tidyr)

Willamette_WSE_Q_daily <- Willamette_long %>% group_by(Date = format(time, "%Y-%m-%d"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE), Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

Willamette_WSE_Q_hourly <- Willamette_long %>% group_by(Date = format(time, "%Y-%m-%d %H"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE), Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

every6hrseq <- seq.POSIXt(min(Willamette_long$time), max(Willamette_long$time), by='6 hour')

Willamette_WSE_Q_6_hrs <-  Willamette_long %>% group_by(Date = cut(time, breaks=every6hrseq), ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

Willamette_WSE_Q_15_min <-  Willamette_long %>% group_by(Date = time, ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

saveRDS(Willamette_WSE_Q_daily, "../input/TOWNS/WSE/Willamette_WSE_Q_daily.rds")
saveRDS(Willamette_WSE_Q_hourly, "../input/TOWNS/WSE/Willamette_WSE_Q_hourly.rds")
saveRDS(Willamette_WSE_Q_6_hrs, "../input/TOWNS/WSE/Willamette_WSE_Q_6_hrs.rds")
saveRDS(Willamette_WSE_Q_15_min, "../input/TOWNS/WSE/Willamette_WSE_Q_15_min.rds")

North Saskatchewan

Load raw WSE data

library(readr)
North_Sask_WSE <- data.frame(read_csv("../input/TOWNS/Raw/Sask_WSE.csv"))
North_Sask_WSE$time <- as.POSIXct(North_Sask_WSE$Time, format = "%m/%d/%Y %H:%M")+3600
attr(North_Sask_WSE$time, "tzone") <- "America/Chicago"
North_Sask_WSE_df <- North_Sask_WSE[,c(11, 2:10)]
colnames(North_Sask_WSE_df)[c(2:10)] = c(1:9)

Load discharge data from Water Survey of Canada

library(tidyhydat)
stationNumber <- "05GG001"
#download_hydat()
#Assumes download_hydat() has been run
North_Sask_Q <- hy_daily_flows(station_number = stationNumber, start_date = as.Date(min(North_Sask_WSE_df$time)), 
                                    end_date = as.Date(max(North_Sask_WSE_df$time)+86400))
North_Sask_Q_df <- data.frame(time = North_Sask_Q$Date, Q = North_Sask_Q$Value)

Combine both data sets (discharge and WSE)

library(dplyr)
library(tidyr)

#filter discharge data to match dates

North_Sask_Q_filt <- North_Sask_Q_df %>% 
  filter(time >= min(North_Sask_WSE_df$time))%>% 
    filter(time <=max(North_Sask_WSE_df$time))%>% left_join(North_Sask_WSE_df)
datatable(North_Sask_Q_filt, caption = "North_Sask")

Plot WSE

library(ggplot2)
North_Sask_long <- North_Sask_Q_filt %>% gather(key = "ID", value = "WSE", -time, -Q)
North_Sask_long$ID <- as.numeric(North_Sask_long$ID)

ggplot(data = North_Sask_long, aes(x = time, y = WSE, color = ID, group = ID))+geom_point()+ggtitle("North_Sask")

Write and save 15-minute, hourly, 6-hourly, and daily
library(dplyr)
library(tidyr)

North_Sask_WSE_Q_daily <- North_Sask_long %>% group_by(Date = format(time, "%Y-%m-%d"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE), Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

North_Sask_WSE_Q_hourly <- North_Sask_long %>% group_by(Date = format(time, "%Y-%m-%d %H"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE), Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

every6hrseq <- seq.POSIXt(min(North_Sask_long$time), max(North_Sask_long$time), by='6 hour')

North_Sask_WSE_Q_6_hrs <-  North_Sask_long %>% group_by(Date = cut(time, breaks=every6hrseq), ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

North_Sask_WSE_Q_15_min <-  North_Sask_long %>% group_by(Date = time, ID) %>%
   summarize(WSE = mean(WSE), Q = mean(Q))%>% spread(value = WSE, key = ID)

saveRDS(North_Sask_WSE_Q_daily, "../input/TOWNS/WSE/North_Sask_WSE_Q_daily.rds")
saveRDS(North_Sask_WSE_Q_hourly, "../input/TOWNS/WSE/North_Sask_WSE_Q_hourly.rds")
saveRDS(North_Sask_WSE_Q_6_hrs, "../input/TOWNS/WSE/North_Sask_WSE_Q_6_hrs.rds")
saveRDS(North_Sask_WSE_Q_15_min, "../input/TOWNS/WSE/North_Sask_WSE_Q_15_min.rds")

Sacramento

Load raw WSE data

library(readr)
library(dplyr)
library(tidyr)
library(DT)
LowSac_vp1 <- read_csv("../input/TOWNS/Raw/LowSac_PT_vp1.csv")
LowSac_vp2 <- read_csv("../input/TOWNS/Raw/LowSac_PT_vp2.csv")
UpSac_vp1 <- read_csv("../input/TOWNS/Raw/UpSac_PT_vp1.csv")
UpSac_vp2 <- read_csv("../input/TOWNS/Raw/UpSac_PT_vp2.csv")

Sacramento_combined <- rbind(LowSac_vp1, LowSac_vp2, UpSac_vp1, UpSac_vp2)

#remove flagged WSE values (pre-install, post-removal, shallow or dry)
Sacramento_combined <- Sacramento_combined[is.na(Sacramento_combined$flag) == "TRUE",]

#check that IDs are descending in terms of WSE
Sacramento_mean <- Sacramento_combined %>% group_by(t_id) %>%
  summarize(WSE = mean(wse, na.rm = TRUE))
Sacramento_mean <- Sacramento_mean[order(Sacramento_mean$WSE),]
Sacramento_mean$ID <- order(Sacramento_mean$WSE)

#Rename PTs so that they are in order
Sacramento_renamed <- merge(Sacramento_combined, Sacramento_mean, by = "t_id")

#Split the river into two reaches (upper and lower)
Sacramento_R1 <- Sacramento_renamed %>% filter(ID <= 10)
Sacramento_R2 <- Sacramento_renamed %>% filter(ID <= 16) %>% filter(ID > 10)
Sacramento_R3 <- Sacramento_renamed %>% filter(ID > 16)


Sacramento_R1_WSE_df <- data.frame(WSE = Sacramento_R1$wse, 
                                      time = as.POSIXct(Sacramento_R1$date_time, format = "%m/%d/%Y %H:%M", tz = "MST"), 
                                      ID = Sacramento_R1$ID)
Sacramento_R2_WSE_df <- data.frame(WSE = Sacramento_R2$wse, 
                                      time = as.POSIXct(Sacramento_R2$date_time, format = "%m/%d/%Y %H:%M", tz = "MST"), 
                                      ID = Sacramento_R2$ID)
Sacramento_R3_WSE_df <- data.frame(WSE = Sacramento_R3$wse, 
                                      time = as.POSIXct(Sacramento_R3$date_time, format = "%m/%d/%Y %H:%M", tz = "MST"), 
                                      ID = Sacramento_R3$ID)

Load discharge data from USGS

library(dataRetrieval)
siteNumber <- "11389500"
parameterCd <- "00060"
convMetric <- 0.0283168466

Sacramento_R1_USGS_Q <- readNWISuv(site = siteNumber, parameterCd = parameterCd, 
                                      startDate = as.Date(min(Sacramento_R1_WSE_df$time)), 
                                      endDate = as.Date(max(Sacramento_R1_WSE_df$time)+86400))

Sacramento_R2_USGS_Q <- readNWISuv(site = siteNumber, parameterCd = parameterCd, 
                                      startDate = as.Date(min(Sacramento_R2_WSE_df$time)), 
                                      endDate = as.Date(max(Sacramento_R2_WSE_df$time)+86400))

Sacramento_R3_USGS_Q <- readNWISuv(site = siteNumber, parameterCd = parameterCd, 
                                      startDate = as.Date(min(Sacramento_R3_WSE_df$time)), 
                                      endDate = as.Date(max(Sacramento_R3_WSE_df$time)+86400))

Sacramento_R1_USGS_Q_df <- data.frame(time = as.POSIXct(Sacramento_R1_USGS_Q$dateTime), 
                                         Q = Sacramento_R1_USGS_Q$X_00060_00000*convMetric)
Sacramento_R2_USGS_Q_df <- data.frame(time = as.POSIXct(Sacramento_R2_USGS_Q$dateTime), 
                                         Q = Sacramento_R2_USGS_Q$X_00060_00000*convMetric)
Sacramento_R3_USGS_Q_df <- data.frame(time = as.POSIXct(Sacramento_R3_USGS_Q$dateTime), 
                                         Q = Sacramento_R3_USGS_Q$X_00060_00000*convMetric)

Combine both data sets (discharge and WSE)

library(dplyr)
library(tidyr)

#filter discharge data to match dates
Sacramento_R1_WSE_spread <- Sacramento_R1_WSE_df %>% spread(value = WSE, key = ID) %>% drop_na()

Sacramento_R1_Q_filt <- Sacramento_R1_USGS_Q_df %>% 
  filter(time >= min(Sacramento_R1_WSE_spread$time))%>% 
  filter(time <=max(Sacramento_R1_WSE_spread$time))%>%
  left_join(Sacramento_R1_WSE_spread)

Sacramento_R2_WSE_spread <- Sacramento_R2_WSE_df %>% spread(value = WSE, key = ID) %>% drop_na()

Sacramento_R2_Q_filt <- Sacramento_R2_USGS_Q_df %>% 
  filter(time >= min(Sacramento_R2_WSE_spread$time))%>% 
  filter(time <=max(Sacramento_R2_WSE_spread$time))%>%
  left_join(Sacramento_R2_WSE_spread)

Sacramento_R3_WSE_spread <- Sacramento_R3_WSE_df %>% spread(value = WSE, key = ID) %>% drop_na()

Sacramento_R3_Q_filt <- Sacramento_R3_USGS_Q_df %>% 
  filter(time >= min(Sacramento_R3_WSE_spread$time))%>% 
  filter(time <=max(Sacramento_R3_WSE_spread$time))%>%
  left_join(Sacramento_R3_WSE_spread)

datatable(Sacramento_R1_Q_filt, caption = "Sacramento Reach One")
datatable(Sacramento_R2_Q_filt, caption = "Sacramento Reach Two")
datatable(Sacramento_R3_Q_filt, caption = "Sacramento Reach Three")

Plot WSE

library(ggplot2)
Sacramento_R1_long <- Sacramento_R1_Q_filt %>% gather(key = "ID", value = "WSE", -time, -Q)
Sacramento_R1_long$ID <- as.numeric(Sacramento_R1_long$ID)

ggplot(data = Sacramento_R1_long, aes(x = time, y = WSE, color = ID, group = ID))+geom_point()+ggtitle("Sacramento Reach One")

Sacramento_R2_long <- Sacramento_R2_Q_filt %>% gather(key = "ID", value = "WSE", -time, -Q)
Sacramento_R2_long$ID <- as.numeric(Sacramento_R2_long$ID)

ggplot(data = Sacramento_R2_long, aes(x = time, y = WSE, color = ID, group = ID))+geom_point()+ggtitle("Sacramento Reach Two")

Sacramento_R3_long <- Sacramento_R3_Q_filt %>% gather(key = "ID", value = "WSE", -time, -Q)
Sacramento_R3_long$ID <- as.numeric(Sacramento_R3_long$ID)

ggplot(data = Sacramento_R3_long, aes(x = time, y = WSE, color = ID, group = ID))+geom_point()+ggtitle("Sacramento Reach Three")

Write and save 15-minute, hourly, 6-hourly, and daily
library(dplyr)
library(tidyr)
#add 30.9942 for corrective WSE 
Sacramento_R1_WSE_Q_daily <- Sacramento_R1_long %>% group_by(Date = format(time, "%Y-%m-%d"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE)+30.9942, Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

Sacramento_R2_WSE_Q_daily <- Sacramento_R2_long %>% group_by(Date = format(time, "%Y-%m-%d"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE)+30.9942, Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

Sacramento_R3_WSE_Q_daily <- Sacramento_R3_long %>% group_by(Date = format(time, "%Y-%m-%d"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE)+30.9942, Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

Sacramento_R1_WSE_Q_hourly <- Sacramento_R1_long %>% group_by(Date = format(time, "%Y-%m-%d %H"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE)+30.9942, Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

Sacramento_R2_WSE_Q_hourly<- Sacramento_R2_long %>% group_by(Date = format(time, "%Y-%m-%d %H"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE)+30.9942, Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

Sacramento_R3_WSE_Q_hourly <- Sacramento_R3_long %>% group_by(Date = format(time, "%Y-%m-%d %H"), ID) %>%
  summarize(WSE = mean(WSE, na.rm = TRUE)+30.9942, Q = mean(Q, na.rm = TRUE))%>% spread(value = WSE, key = ID)

R1_every6hrseq <- seq.POSIXt(min(Sacramento_R1_long$time), max(Sacramento_R1_long$time), by='6 hour')
R2_every6hrseq <- seq.POSIXt(min(Sacramento_R2_long$time), max(Sacramento_R2_long$time), by='6 hour')
R3_every6hrseq <- seq.POSIXt(min(Sacramento_R3_long$time), max(Sacramento_R3_long$time), by='6 hour')

Sacramento_R1_WSE_Q_6_hrs <-  Sacramento_R1_long %>% group_by(Date = cut(time, breaks=R1_every6hrseq), ID) %>%
   summarize(WSE = mean(WSE)+30.9942, Q = mean(Q))%>% spread(value = WSE, key = ID)

Sacramento_R2_WSE_Q_6_hrs <-  Sacramento_R2_long %>% group_by(Date = cut(time, breaks=R2_every6hrseq), ID) %>%
   summarize(WSE = mean(WSE)+30.9942, Q = mean(Q))%>% spread(value = WSE, key = ID)

Sacramento_R3_WSE_Q_6_hrs <-  Sacramento_R3_long %>% group_by(Date = cut(time, breaks=R3_every6hrseq), ID) %>%
   summarize(WSE = mean(WSE)+30.9942, Q = mean(Q))%>% spread(value = WSE, key = ID)

Sacramento_R1_WSE_Q_15_min <-  Sacramento_R1_long %>% group_by(Date = time, ID) %>%
   summarize(WSE = mean(WSE)+30.9942, Q = mean(Q))%>% spread(value = WSE, key = ID)

Sacramento_R2_WSE_Q_15_min <-  Sacramento_R2_long %>% group_by(Date = time, ID) %>%
   summarize(WSE = mean(WSE)+30.9942, Q = mean(Q))%>% spread(value = WSE, key = ID)

Sacramento_R3_WSE_Q_15_min <-  Sacramento_R3_long %>% group_by(Date = time, ID) %>%
   summarize(WSE = mean(WSE)+30.9942, Q = mean(Q))%>% spread(value = WSE, key = ID)

saveRDS(Sacramento_R1_WSE_Q_daily, "../input/TOWNS/WSE/Sacramento_R1_WSE_Q_daily.rds")
saveRDS(Sacramento_R1_WSE_Q_hourly, "../input/TOWNS/WSE/Sacramento_R1_WSE_Q_hourly.rds")
saveRDS(Sacramento_R1_WSE_Q_6_hrs, "../input/TOWNS/WSE/Sacramento_R1_WSE_Q_6_hrs.rds")
saveRDS(Sacramento_R1_WSE_Q_15_min, "../input/TOWNS/WSE/Sacramento_R1_WSE_Q_15_min.rds")

saveRDS(Sacramento_R2_WSE_Q_daily, "../input/TOWNS/WSE/Sacramento_R2_WSE_Q_daily.rds")
saveRDS(Sacramento_R2_WSE_Q_hourly, "../input/TOWNS/WSE/Sacramento_R2_WSE_Q_hourly.rds")
saveRDS(Sacramento_R2_WSE_Q_6_hrs, "../input/TOWNS/WSE/Sacramento_R2_WSE_Q_6_hrs.rds")
saveRDS(Sacramento_R2_WSE_Q_15_min, "../input/TOWNS/WSE/Sacramento_R2_WSE_Q_15_min.rds")

saveRDS(Sacramento_R3_WSE_Q_daily, "../input/TOWNS/WSE/Sacramento_R3_WSE_Q_daily.rds")
saveRDS(Sacramento_R3_WSE_Q_hourly, "../input/TOWNS/WSE/Sacramento_R3_WSE_Q_hourly.rds")
saveRDS(Sacramento_R3_WSE_Q_6_hrs, "../input/TOWNS/WSE/Sacramento_R3_WSE_Q_6_hrs.rds")
saveRDS(Sacramento_R3_WSE_Q_15_min, "../input/TOWNS/WSE/Sacramento_R3_WSE_Q_15_min.rds")